{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "171f1159",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import xlrd\n",
    "from openpyxl import Workbook\n",
    "\n",
    "# 指定包含.xls文件的文件夹\n",
    "input_folder = 'D:\\日报\\拜耳店\\新建文件夹'\n",
    "# 指定保存.xlsx文件的文件夹\n",
    "output_folder = 'D:\\日报\\拜耳店\\新建文件夹'\n",
    "\n",
    "# 遍历输入文件夹中的所有文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件的完整路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, filename.replace('.xls', '.xlsx'))\n",
    "\n",
    "        # 打开.xls文件并创建一个新的.xlsx文件\n",
    "        xls_workbook = xlrd.open_workbook(input_path)\n",
    "        xlsx_workbook = Workbook()\n",
    "\n",
    "        # 遍历.xls文件中的每个工作表并复制到新的.xlsx文件\n",
    "        for sheet_name in xls_workbook.sheet_names():\n",
    "            xls_sheet = xls_workbook.sheet_by_name(sheet_name)\n",
    "            xlsx_sheet = xlsx_workbook.active if sheet_name == xls_workbook.sheet_names()[0] else xlsx_workbook.create_sheet(sheet_name)\n",
    "\n",
    "            for row in range(xls_sheet.nrows):\n",
    "                for col in range(xls_sheet.ncols):\n",
    "                    xlsx_sheet.cell(row=row+1, column=col+1, value=xls_sheet.cell_value(row, col))\n",
    "\n",
    "        # 保存新的.xlsx文件\n",
    "        xlsx_workbook.save(output_path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a76a53c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import xlwings as xw\n",
    "\n",
    "# 指定包含.xls文件的文件夹\n",
    "input_folder = 'D:/日报/拜耳店/新建文件夹'\n",
    "# 指定保存.xlsx文件的文件夹\n",
    "output_folder = 'D:/日报/拜耳店/新建文件夹'\n",
    "\n",
    "# 遍历输入文件夹中的所有文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件的完整路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, filename.replace('.xls', '.xlsx'))\n",
    "\n",
    "        # 打开Excel文件\n",
    "        app = xw.App(visible=False)\n",
    "        wb = app.books.open(input_path)\n",
    "        sheet = wb.sheets[0]\n",
    "\n",
    "        # 另存为.xlsx格式并保留合并单元格\n",
    "        wb.save(output_path)\n",
    "        wb.close()\n",
    "        app.quit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a7e4897",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import xlwings as xw\n",
    "import time\n",
    "# 指定包含.xls文件的文件夹\n",
    "input_folder = input('转换的文件路径：')\n",
    "# 指定保存.xlsx文件的文件夹\n",
    "output_folder = input('保存的路径：')\n",
    "\n",
    "# 起始时间\n",
    "start_time = time.time()\n",
    "# 计数器\n",
    "file_num = 0\n",
    "\n",
    "# 遍历输入文件夹中的所有文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    # 计数器加1；\n",
    "    file_num += 1\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件的完整路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, filename.replace('.xls', '.xlsx'))\n",
    "\n",
    "        # 打开Excel文件\n",
    "        app = xw.App(visible=False)\n",
    "        wb = app.books.open(input_path)\n",
    "        sheet = wb.sheets[0]\n",
    "\n",
    "        # 另存为.xlsx格式并保留合并单元格\n",
    "        wb.save(output_path)\n",
    "        wb.close()\n",
    "        app.quit()\n",
    "\n",
    "        # 删除原始.xls文件a\n",
    "        os.remove(input_path)\n",
    "        \n",
    "# 结束时间；\n",
    "end_time = time.time()\n",
    "# 计算耗时；\n",
    "delta_time = end_time - start_time\n",
    "print(f\"数据表格式转换耗时{delta_time: .3f}秒，共计转换{file_num}张表格\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9d35681d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "无法处理文件 商品SKU数据-21-2023-09-28.xls: [WinError 32] 另一个程序正在使用此文件，进程无法访问。: 'D:/日报/拜耳店/新建文件夹\\\\商品SKU数据-21-2023-09-28.xls'\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import xlwings as xw\n",
    "\n",
    "# 指定包含.xls文件的文件夹\n",
    "input_folder = 'D:/日报/拜耳店/新建文件夹'\n",
    "# 指定保存.xlsx文件的文件夹\n",
    "output_folder = 'D:/日报/拜耳店/新建文件夹'\n",
    "\n",
    "# 遍历输入文件夹中的所有文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件的完整路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, filename.replace('.xls', '.xlsx'))\n",
    "\n",
    "        try:\n",
    "            # 打开Excel文件\n",
    "            app = xw.App(visible=False)\n",
    "            wb = app.books.open(input_path)\n",
    "            sheet = wb.sheets[0]\n",
    "\n",
    "            # 另存为.xlsx格式并保留合并单元格\n",
    "            wb.save(output_path)\n",
    "            wb.close()\n",
    "            app.quit()\n",
    "\n",
    "            # 强制删除原始.xls文件\n",
    "            os.remove(input_path)\n",
    "        except PermissionError as e:\n",
    "            print(f\"无法处理文件 {filename}: {e}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "089d6ba9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import xlwings as xw\n",
    "import time\n",
    "\n",
    "# Input and output folders\n",
    "input_folder = input('转换的文件路径：')\n",
    "output_folder = input('保存的路径：')\n",
    "\n",
    "# Start time\n",
    "start_time = time.time()\n",
    "file_num = 0\n",
    "\n",
    "# Iterate over files in the input folder\n",
    "for filename in os.listdir(input_folder):\n",
    "    # Increment the file counter\n",
    "    file_num += 1\n",
    "    if filename.endswith('.xls'):\n",
    "        # Build input and output file paths\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, os.path.splitext(filename)[0] + '.xlsx')\n",
    "\n",
    "        # Process the file\n",
    "        with xw.App(visible=False) as app:\n",
    "            wb = app.books.open(input_path)\n",
    "            sheet = wb.sheets[0]\n",
    "            wb.save(output_path)\n",
    "            wb.close()\n",
    "        \n",
    "        # Delete the original .xls file\n",
    "        os.remove(input_path)\n",
    "\n",
    "        # Display progress\n",
    "        print(f\"转换完成：{filename}\")\n",
    "\n",
    "# End time\n",
    "end_time = time.time()\n",
    "delta_time = end_time - start_time\n",
    "print(f\"数据表格式转换耗时{delta_time:.3f}秒，共计转换{file_num}张表格\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3859f86d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import xlwings as xw\n",
    "import time\n",
    "\n",
    "# 输入和输出文件夹\n",
    "input_folder = input('转换的文件路径：')\n",
    "output_folder = input('保存的路径：')\n",
    "\n",
    "# 开始时间\n",
    "start_time = time.time()\n",
    "file_num = 0\n",
    "\n",
    "# 遍历输入文件夹中的文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    # 增加文件计数\n",
    "    file_num += 1\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, os.path.splitext(filename)[0] + '.xlsx')\n",
    "\n",
    "        # 处理文件\n",
    "        with xw.App(visible=False) as app:\n",
    "            wb = app.books.open(input_path)\n",
    "            sheet = wb.sheets[0]\n",
    "            wb.save(output_path)\n",
    "            wb.close()\n",
    "        \n",
    "        # 删除原始 .xls 文件\n",
    "        os.remove(input_path)\n",
    "\n",
    "        # 显示进度\n",
    "        print(f\"转换完成：{filename}\")\n",
    "\n",
    "# 结束时间\n",
    "end_time = time.time()\n",
    "delta_time = end_time - start_time\n",
    "print(f\"数据表格式转换耗时{delta_time:.3f}秒，共计转换{file_num}张表格\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "70b85f1b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "转换的文件路径：D:\\日报\\拜耳店\\新建文件夹 (2)\n",
      "保存的路径：D:\\日报\\拜耳店\\新建文件夹 (2)\n",
      "转换完成：【生意参谋平台】商品_所有终端_2023-10-16_2023-10-16.xlsx\n",
      "转换完成：商品SKU数据-07-2023-09-28.xlsx\n",
      "转换完成：商品SKU数据-10-2023-09-28.xlsx\n",
      "转换完成：商品SKU数据-10-2023-09-28dasdasda.xlsx\n",
      "转换完成：商品SKU数据-16-2023-09-28.xlsx\n",
      "转换完成：商品SKU数据-19-2023-09-28.xlsx\n",
      "转换完成：商品SKU数据-20-2023-09-28.xlsx\n",
      "转换完成：商品SKU数据-21-2023-09-28.xlsx\n",
      "转换完成：商品SKU数据-22-2023-09-28.xlsx\n",
      "数据表格式转换耗时7.162秒，共计转换9张表格\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import xlwings as xw\n",
    "import time\n",
    "\n",
    "# 输入和输出文件夹\n",
    "input_folder = input('转换的文件路径：')\n",
    "output_folder = input('保存的路径：')\n",
    "\n",
    "# 开始时间\n",
    "start_time = time.time()\n",
    "file_num = 0\n",
    "\n",
    "# 遍历输入文件夹中的文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    # 增加文件计数\n",
    "    file_num += 1\n",
    "    if filename.endswith('.xlsx'):\n",
    "        # 构建输入和输出文件路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, os.path.splitext(filename)[0] + '.xls')\n",
    "\n",
    "        # 处理文件\n",
    "        with xw.App(visible=False) as app:\n",
    "            wb = app.books.open(input_path)\n",
    "            sheet = wb.sheets[0]\n",
    "            wb.save(output_path)\n",
    "            wb.close()\n",
    "        \n",
    "        # 删除原始 .xlsx 文件\n",
    "        os.remove(input_path)\n",
    "\n",
    "        # 显示进度\n",
    "        print(f\"转换完成：{filename}\")\n",
    "\n",
    "# 结束时间\n",
    "end_time = time.time()\n",
    "delta_time = end_time - start_time\n",
    "print(f\"数据表格式转换耗时{delta_time:.3f}秒，共计转换{file_num}张表格\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f5f099dd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "转换的文件路径：D:\\日报\\拜耳店\\新建文件夹 (2)\n",
      "保存的路径：D:\\日报\\拜耳店\\新建文件夹 (2)\n",
      "转换完成：【生意参谋平台】商品_所有终端_2023-10-16_2023-10-16.xls\n",
      "转换完成：商品SKU数据-07-2023-09-28.xls\n",
      "转换完成：商品SKU数据-10-2023-09-28.xls\n",
      "转换完成：商品SKU数据-10-2023-09-28dasdasda.xls\n",
      "转换完成：商品SKU数据-16-2023-09-28.xls\n",
      "转换完成：商品SKU数据-19-2023-09-28.xls\n",
      "转换完成：商品SKU数据-20-2023-09-28.xls\n",
      "转换完成：商品SKU数据-21-2023-09-28.xls\n",
      "转换完成：商品SKU数据-22-2023-09-28.xls\n",
      "数据表格式转换耗时1.041秒，共计转换9张表格\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# 输入和输出文件夹\n",
    "input_folder = input('转换的文件路径：')\n",
    "output_folder = input('保存的路径：')\n",
    "\n",
    "# 开始时间\n",
    "start_time = time.time()\n",
    "file_num = 0\n",
    "\n",
    "# 遍历输入文件夹中的文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    # 增加文件计数\n",
    "    file_num += 1\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, os.path.splitext(filename)[0] + '.xlsx')\n",
    "\n",
    "        # 读取 .xls 文件并保存为 .xlsx\n",
    "        df = pd.read_excel(input_path)\n",
    "        df.to_excel(output_path, index=False)\n",
    "\n",
    "        # 显示进度\n",
    "        print(f\"转换完成：{filename}\")\n",
    "\n",
    "# 结束时间\n",
    "end_time = time.time()\n",
    "delta_time = end_time - start_time\n",
    "print(f\"数据表格式转换耗时{delta_time:.3f}秒，共计转换{file_num}张表格\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e800a22",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# 输入和输出文件夹\n",
    "input_folder = input('转换的文件路径：')\n",
    "output_folder = input('保存的路径：')\n",
    "\n",
    "# 开始时间\n",
    "start_time = time.time()\n",
    "file_num = 0\n",
    "\n",
    "# 遍历输入文件夹中的文件\n",
    "for filename in os.listdir(input_folder):\n",
    "    # 增加文件计数\n",
    "    file_num += 1\n",
    "    if filename.endswith('.xls'):\n",
    "        # 构建输入和输出文件路径\n",
    "        input_path = os.path.join(input_folder, filename)\n",
    "        output_path = os.path.join(output_folder, os.path.splitext(filename)[0] + '.xlsx')\n",
    "\n",
    "        # 读取 .xls 文件\n",
    "        xls = pd.ExcelFile(input_path)\n",
    "        df = pd.read_excel(xls, header=None)\n",
    "        \n",
    "        # 获取第一行的合并单元格\n",
    "        first_row = xls.parse(sheet_name=0, header=None).iloc[0]\n",
    "\n",
    "        # 保存为 .xlsx 文件，保留第一行的合并单元格\n",
    "        with pd.ExcelWriter(output_path, engine='xlsxwriter') as writer:\n",
    "            df.to_excel(writer, sheet_name='Sheet1', index=False, header=False)\n",
    "            writer.sheets['Sheet1'].set_row(0, None, None, {'level': 1, 'hidden': True})\n",
    "        \n",
    "        # 删除原始 .xls 文件\n",
    "        os.remove(input_path)\n",
    "\n",
    "        # 显示进度\n",
    "        print(f\"转换完成：{filename}\")\n",
    "\n",
    "# 结束时间\n",
    "end_time = time.time()\n",
    "delta_time = end_time - start_time\n",
    "print(f\"数据表格式转换耗时{delta_time:.3f}秒，共计转换{file_num}张表格\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3ade495",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
